/* Formatted on 12/07/2011 17:03:14 (QP5 v5.163.1008.3004) */
DROP SEQUENCE FLUX_SEQ;

CREATE SEQUENCE FLUX_SEQ
   START WITH 1
   INCREMENT BY 1
   MAXVALUE 999999999999999999999999999
   MINVALUE 1
   CYCLE
   NOCACHE
   NOORDER;
   
DROP SEQUENCE RESERVATION_SEQ;
CREATE SEQUENCE RESERVATION_SEQ
   START WITH 1
   INCREMENT BY 1
   MAXVALUE 999999999999999999999999999
   MINVALUE 1
   CYCLE
   NOCACHE
   NOORDER;

ALTER TABLE FLUX
 DROP PRIMARY KEY CASCADE;

DROP TABLE FLUX CASCADE CONSTRAINTS;

CREATE TABLE FLUX
(
   ID                 NUMBER,
   DATEINSERTION      TIMESTAMP (6) WITH TIME ZONE default current_timestamp,
   CDEINTERMEDIAIRE   VARCHAR2 (50 CHAR),
   CDEPORTEFEUILLE    VARCHAR2 (100 CHAR),
   IDCLIENT           VARCHAR2 (50 CHAR),
   IDSINISTRE         VARCHAR2 (50 CHAR),
   IDCONTRAT          VARCHAR2 (50 CHAR),
   EMETTEUR           VARCHAR2 (30 CHAR),
   TYPEFLUX           VARCHAR2 (20 CHAR),
   MODEENVOI          VARCHAR2 (10 CHAR),
   DATEENVOI          TIMESTAMP (6) WITH TIME ZONE,
   DATEACK            TIMESTAMP (6) WITH TIME ZONE,
   NUMRESERVATION     VARCHAR (100),
   CONTENT            VARCHAR2 (4000 CHAR),
   VERSION            NUMBER DEFAULT 0
);

DROP INDEX ID_CLIENT_IDX;
CREATE INDEX ID_CLIENT_IDX
   ON FLUX (IDCLIENT)
   LOGGING
   NOPARALLEL
   COMPUTE STATISTICS;

DROP INDEX CDE_INTERMEDIAIRE_IDX;
CREATE INDEX CDE_INTERMEDIAIRE_IDX
   ON FLUX (CDEINTERMEDIAIRE)
   LOGGING
   NOPARALLEL
   COMPUTE STATISTICS;


CREATE OR REPLACE TRIGGER "FLUX_SEQ_INSERT_TRG"
   BEFORE INSERT
   ON FLUX
   FOR EACH ROW
DECLARE
   sas_id   NUMBER;
BEGIN
   SELECT FLUX_SEQ.NEXTVAL INTO sas_id FROM DUAL;

   :new.ID := sas_id;
END;
/

ALTER TABLE FLUX ADD (
  CONSTRAINT FLUX_PK
  PRIMARY KEY
  (ID));